package com.etc;

import java.sql.*;

public class Jdbc722 {

    //        2.获得数据库的连接
    static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
    static String usre = "root";
    static String password = "123456";
    static Connection con = null;

    public static Connection getConnection() throws SQLException {

        try {
//            把数据库连接起来
            Class.forName("com.mysql.jdbc.Driver");
            //      数据库链接   （创建对象）
            con = DriverManager.getConnection(url, usre, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return con;
    }

    //        调用增删改查方法
    public static void main(String[] args) throws SQLException {
//       add();
//      update();
//      select();
//        delete();
//        select1();
//        select2();
//        select3();
//        select4();
        select5();


    }

    public static void select() throws SQLException {
//        获得statement对象，操作数据库返回结果
        String sql = "select * from course";


        //发送器(增删改查)
        PreparedStatement preparedStatement = Jdbc722.getConnection().prepareStatement(sql);
        ResultSet resultSet1 = preparedStatement.executeQuery();
        //        处理数据（增删改查）
        while (resultSet1.next()) {
            String cno = resultSet1.getString("cno");
            String cname = resultSet1.getString("cname");
            String tno = resultSet1.getString("tno");

            System.out.println(cno + "\t" + cname + "\t" + tno);
        }
        con.close();
    }

    public static void add() throws SQLException {
        String sql = "insert into course(cno,cname,tno) values(?,?,?)";
        Connection connection = Jdbc722.getConnection();
        PreparedStatement p = connection.prepareStatement(sql);
        p.setString(1, "8-223");
        p.setString(2, "物理");
        p.setString(3, "777");
        int i = p.executeUpdate();
        System.out.println(i == 1 ? "添加成功" : "添加失败");


    }

    public static void delete() throws SQLException {
        String sql = "delete from course where cno=?";
        Connection connection = Jdbc722.getConnection();
        PreparedStatement p = connection.prepareStatement(sql);
//        删除一个
        p.setString(1, "3-105");
        int i = p.executeUpdate();
        System.out.println(i == 1 ? "删除成功" : "删除失败");

    }

    public static void update() throws SQLException {
        String sql = "update course set cname =? where cno=?";
        Connection connection = Jdbc722.getConnection();
        PreparedStatement p = connection.prepareStatement(sql);
        p.setString(1, "上下五千年");
//        什么类型写什么类型
        p.setString(2, "3-105");
        int i = p.executeUpdate();
        System.out.println(i == 1 ? "更新成功" : "更新失败");

    }

    public static void select1() throws SQLException {
        String sql = "select * from score where Degree between ? and ?";
        PreparedStatement p = Jdbc722.getConnection().prepareStatement(sql);
        p.setInt(1, 60);
        p.setInt(2, 80);
        ResultSet rs = p.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("sno")
                    + "\t" + rs.getString("cno")
                    + "\t" + rs.getString("degree"));
        }

    }

    public static void select2() throws SQLException {
        String sql = "select * from student where classs =? or ssex=?";
        PreparedStatement p = Jdbc722.getConnection().prepareStatement(sql);
        p.setString(1, "95031");
        p.setString(2, "女");
        ResultSet re = p.executeQuery();
        while (re.next()) {
            System.out.println(re.getString("sno") +
                    "\t" + re.getString("sname") +
                    "\t" + re.getString("ssex") +
                    "\t" + re.getString("sbirthday") +
                    "\t" + re.getString("classs"));
        }


    }

    public static void select3() throws SQLException {
        String sql = "select  * from student  order by classs desc";
        PreparedStatement p = Jdbc722.getConnection().prepareStatement(sql);
        ResultSet r = p.executeQuery();
        while (r.next()) {
            String sno = r.getString("sno");
            String sname = r.getString("sname");
            String ssex = r.getString("ssex");
            String sbirthday = r.getString("sbirthday");
            String classs = r.getString("classs");
            System.out.println(sno + "\t" + sname + "\t" + ssex + "\t" + sbirthday + "\t" + classs);

        }
        con.close();
    }

    public static void select4() throws SQLException {
        String sql = "select * from score order by cno asc,degree desc";
        PreparedStatement p = Jdbc722.getConnection().prepareStatement(sql);
        ResultSet r = p.executeQuery();
        while (r.next()) {
            String sno = r.getString("sno");
            String cno = r.getString("cno");
            String degree = r.getString("degree");
            System.out.println(sno + "\t" + cno + "\t" + degree + "\t");

        }
    }

    public static void select5() throws SQLException {
        String sql = "select classs,count(*) num from student where classs=?";
        PreparedStatement p = Jdbc722.getConnection().prepareStatement(sql);

        p.setString(1, "95033");
        ResultSet r = p.executeQuery();
        while (r.next()) {
            System.out.println(r.getString("classs") + "\t" + r.getInt("num"));
        }
    }
}







//        4.处理数据
//        Statement statement = con.createStatement();
//        ResultSet resultSet = statement.executeQuery(sql);
//        while (resultSet.next()){
//            System.out.println(resultSet.getString("cno"));
//        }
//        resultSet.close();
//        statement.close();
//        con.close();



